package FileTable::Links;
use strict;
#
# A package that helps manage links between FileTable records.
# Its first usage is the case where a FileTable record field has a list of
#pointers (keys) from another table, and we wish to do easy lookups in
#both directions. Classic case: managers for places.
#
# The technique is to create a "link" table that contains from and to.
# Derivations of this class may attach more fields to the link, but
#that's not needed for its first application.
# Think of it as a Relationship in an ER diagram.
#
# Suggestion: Tablenames should be named link_somerelation....
# 
use FileTable;
use vars qw($DEBUG);

sub new {
    my $class = shift;
    my $tablename = shift;

    die "Usage error" if !$tablename;

    my $self = {};
    $$self{tablename} = $tablename;
    bless $self, $class;
    return $self;
}

sub sqlcreatestatement {
# separate statement so that it can be overridden in derived classes
    my $self = shift;
    
    my $statement = "create table $$self{tablename} (
       fromkey INT NOT NULL,
       tokey INT NOT NULL,
       UNIQUE (fromkey, tokey)
    )";
    return $statement;
}

sub tableexists {
# ensure that the table exists. Will create or die();
    my $self = shift;
    my $status;
    if (!$$self{tableexists}) {
	my $table = $$self{tablename};
	my $dbh = FileTable::dbhandle();
	my $sth = $dbh->prepare("LISTFIELDS $table");
	$sth->{PrintError} = 0; # Don't go onscreen if it fails
	$status = $sth->execute;
	if (!$status) {
	    if ($sth->errstr =~ /Unknown table/ | $sth->errstr =~ /doesn't exist/) { # '
		my $statement = $self->sqlcreatestatement();
                $DEBUG && warn "Create statement: $statement\n";
                $dbh->do($statement)
                    || die "Create failed";
                $sth = $dbh->prepare("LISTFIELDS $table");
                $sth->execute || die "Listfields $table failed after create";
            } else {
		die "listfields $table failed - ", $sth->errstr;
	    }
        }
	$$self{tableexists} = 1;
    }
}

sub removeall {
# Clean table. Chiefly good for testing;
    my $self = shift;
    my $dbh = FileTable::dbhandle();
    $self->tableexists();
    $dbh->do("delete from $$self{tablename}") || die "Removeall failed";
}

sub addonelink {
    my ($self,  $fromkey, $tokey) = @_;
    $fromkey = $fromkey + 0; # Weak syntax check
    $tokey = $tokey + 0; 
    my $dbh = FileTable::dbhandle();
    $self->tableexists();
    # warn "Adding link from $fromkey to $tokey\n";
    # use REPLACE to avoid an error message for existing links
    my $sth = $dbh->prepare("REPLACE $$self{tablename} SET fromkey=?, tokey=?");
    my $status = $sth->execute($fromkey,  $tokey);
    die "Insert failed" if !$status;
}
    

sub linksfrom {
    my ($self, $fromkey) = @_;
    my $dbh = FileTable::dbhandle();
    my @restab;
    $self->tableexists();
    $fromkey = $fromkey + 0; # weak syntax check
    my $sth = $dbh->prepare("SELECT tokey from $$self{tablename} where fromkey = ? ORDER BY tokey");
    $sth->execute($fromkey) || die "select failed";
    while (1) {
        my @res = $sth->fetchrow_array();
        last if $#res == -1;
        die "Wrong number of elms: $#res" if $#res != 0;
        push(@restab, $res[0]);
    }
    @restab;
}

sub linksto {
    my ($self, $tokey) = @_;
    my $dbh = FileTable::dbhandle();
    my @restab;
    $self->tableexists();
    my $sth = $dbh->prepare("SELECT fromkey from $$self{tablename} where tokey = ?");
    $sth->execute($tokey) || die "select failed";
    while (1) {
        my @res = $sth->fetchrow_array();
        last if $#res == -1;
        die "Wrong number of elms: $#res" if $#res != 0;
        push(@restab, $res[0]);
    }
    @restab;
}

sub removelinksfrom {
    my ($self, $fromkey) = @_;
    my $dbh = FileTable::dbhandle();
    $self->tableexists();
    $fromkey = $fromkey + 0; # weak syntax check
    $dbh->do("DELETE from $$self{tablename} where fromkey = $fromkey");
}

sub removelinksto {
    my ($self, $tokey) = @_;
    my $dbh = FileTable::dbhandle();
    $self->tableexists();
    $tokey = $tokey + 0; # weak syntax check
    $dbh->do("DELETE from $$self{tablename} where tokey = $tokey");
}

sub setlinksfrom {
# Set the list of links from this key to a certain value
# Remove old links.
    my $self = shift;
    my $fromkey = shift;
    my @tokeys = @_;

    $self->removelinksfrom($fromkey);
    for my $tokey (@tokeys) {
	$self->addonelink($fromkey, $tokey);
    }

}

sub setlinksto {
# Set the list of links to this key to a certain value
# Remove old links.
# (note: for any given class, only one of these is likely to be useful..)
    my $self = shift;
    my $tokey = shift;
    my @fromkeys = @_;

    $self->removelinksto($tokey);
    for my $fromkey (@fromkeys) {
	$self->addonelink($fromkey, $tokey);
    }

}

1;
